from sqlalchemy.orm import Session
from sqlalchemy import func, case
from typing import List, Optional, Dict, Any
from app.models.product import Product
from app.schemas.product import ProductCreate, ProductUpdate
import random as py_random

class ProductCRUD:
    def create_product(self, db: Session, obj_in: ProductCreate) -> Product:
        """创建产品记录"""
        db_obj = Product(**obj_in.dict())
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    def get_product(self, db: Session, product_id: int) -> Optional[Product]:
        """根据ID获取产品"""
        return db.query(Product).filter(Product.id == product_id).first()
    
    def get_products_by_workflow(self, db: Session, workflow_id: int, skip: int = 0, limit: int = 100) -> List[Product]:
        """获取工作流相关的产品列表"""
        return db.query(Product).filter(
            Product.workflow_id == workflow_id
        ).offset(skip).limit(limit).all()
    
    def get_products_by_user(self, db: Session, user_id: int, skip: int = 0, limit: int = 100) -> List[Product]:
        """获取用户的产品列表"""
        return db.query(Product).filter(
            Product.user_id == user_id
        ).order_by(Product.created_at.desc()).offset(skip).limit(limit).all()
    
    def get_products_count_by_workflow(self, db: Session, workflow_id: int) -> int:
        """获取工作流的产品数量"""
        return db.query(Product).filter(Product.workflow_id == workflow_id).count()
    
    def get_products_count_by_user(self, db: Session, user_id: int) -> int:
        """获取用户的产品总数"""
        return db.query(Product).filter(Product.user_id == user_id).count()

    # 新增：产品瀑布流查询（按创建时间倒序分页）
    # 修改：产品瀑布流查询（支持随机排序）
    def get_products_waterfall(self, db: Session, page: int = 1, page_size: int = 20, seed: Optional[str] = None) -> tuple[list[Product], int]:
        """获取瀑布流产品数据"""
        if seed:
            # 有种子值：使用稳定随机排序
            # 1. 获取所有产品ID
            all_ids = [row[0] for row in db.query(Product.id).all()]
            total = len(all_ids)
            
            # 2. 使用种子值打乱ID顺序
            py_random.seed(seed)
            py_random.shuffle(all_ids)
            
            # 3. 根据分页获取对应的ID
            offset = (page - 1) * page_size
            page_ids = all_ids[offset:offset + page_size]
            
            # 4. 根据ID顺序查询产品
            if page_ids:
                # 修复：使用正确的case语法 - 展开列表作为位置参数
                order_case = case(
                    *[(Product.id == id_val, idx) for idx, id_val in enumerate(page_ids)],
                    else_=len(page_ids)
                )
                products = db.query(Product).filter(Product.id.in_(page_ids)).order_by(order_case).all()
            else:
                products = []
                
            return products, total
        else:
            # 没有种子值：按创建时间倒序
            query = db.query(Product).order_by(Product.created_at.desc())
            total = query.count()
            offset = (page - 1) * page_size
            products = query.offset(offset).limit(page_size).all()
            return products, total
    
    def update_product(self, db: Session, product_id: int, obj_in: ProductUpdate) -> Optional[Product]:
        """更新产品信息"""
        db_obj = self.get_product(db, product_id)
        if db_obj:
            update_data = obj_in.dict(exclude_unset=True)
            for field, value in update_data.items():
                setattr(db_obj, field, value)
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def delete_product(self, db: Session, product_id: int) -> bool:
        """删除产品"""
        db_obj = self.get_product(db, product_id)
        if db_obj:
            db.delete(db_obj)
            db.commit()
            return True
        return False
    
    def delete_products_batch(self, db: Session, product_ids: List[int], user_id: int) -> Dict[str, Any]:
        """批量删除产品"""
        if not product_ids:
            return {"success_count": 0, "failed_count": 0, "failed_ids": []}
        
        # 查询要删除的产品，验证权限
        products = db.query(Product).filter(
            Product.id.in_(product_ids),
            Product.user_id == user_id  # 只能删除自己的产品
        ).all()
        
        existing_ids = [p.id for p in products]
        failed_ids = [pid for pid in product_ids if pid not in existing_ids]
        
        # 批量删除
        if existing_ids:
            db.query(Product).filter(
                Product.id.in_(existing_ids),
                Product.user_id == user_id
            ).delete(synchronize_session=False)
            db.commit()
        
        return {
            "success_count": len(existing_ids),
            "failed_count": len(failed_ids),
            "failed_ids": failed_ids
        }
    
    def check_workflow_products_exist(self, db: Session, workflow_id: int) -> bool:
        """检查工作流是否已经有产品数据"""
        count = db.query(Product).filter(Product.workflow_id == workflow_id).count()
        return count > 0

    # 新增：获取某工作流下所有产品（不分页）
    def get_all_products_by_workflow(self, db: Session, workflow_id: int) -> List[Product]:
        return db.query(Product).filter(Product.workflow_id == workflow_id).all()

    # 新增：判断来源是否已存在
    def exists_by_source(self, db: Session, workflow_id: int, source_type: str, source_index: int) -> bool:
        return db.query(Product.id).filter(
            Product.workflow_id == workflow_id,
            Product.source_type == source_type,
            Product.source_index == source_index
        ).first() is not None
    def exists_by_workflow_and_index(self, db: Session, workflow_id: int, source_index: int) -> bool:
        """判断某工作流下给定索引的产品是否已存在（忽略来源类型）"""
        return db.query(Product.id).filter(
            Product.workflow_id == workflow_id,
            Product.source_index == source_index
        ).first() is not None

    # 新增：按 entity_id 获取产品
    def get_by_entity_id(self, db: Session, entity_id: str) -> Optional[Product]:
        return db.query(Product).filter(Product.entity_id == entity_id).order_by(Product.created_at.desc()).first()

    def delete_products_batch_admin(self, db: Session, product_ids: List[int]) -> Dict[str, Any]:
        """管理员批量删除产品（无用户权限限制）"""
        if not product_ids:
            return {"success_count": 0, "failed_count": 0, "failed_ids": []}
        
        # 查询要删除的产品（管理员可以删除任何产品）
        products = db.query(Product).filter(
            Product.id.in_(product_ids)
        ).all()
        
        existing_ids = [p.id for p in products]
        failed_ids = [pid for pid in product_ids if pid not in existing_ids]
        
        # 批量删除
        if existing_ids:
            db.query(Product).filter(
                Product.id.in_(existing_ids)
            ).delete(synchronize_session=False)
            db.commit()
        
        return {
            "success_count": len(existing_ids),
            "failed_count": len(failed_ids),
            "failed_ids": failed_ids
        }

    def delete_products_by_workflow(self, db: Session, workflow_id: int) -> Dict[str, Any]:
        """根据工作流ID删除所有相关产品"""
        try:
            # 查询该工作流下的所有产品
            products = db.query(Product).filter(Product.workflow_id == workflow_id).all()
            
            if not products:
                return {
                    "success": True,
                    "deleted_count": 0,
                    "message": "该工作流下没有产品数据"
                }
            
            product_count = len(products)
            
            # 批量删除
            db.query(Product).filter(Product.workflow_id == workflow_id).delete(synchronize_session=False)
            db.commit()
            
            return {
                "success": True,
                "deleted_count": product_count,
                "message": f"成功删除工作流 {workflow_id} 下的 {product_count} 个产品"
            }
            
        except Exception as e:
            db.rollback()
            return {
                "success": False,
                "deleted_count": 0,
                "message": f"删除失败: {str(e)}"
            }

    def search_products_by_tags(self, db: Session, tags: List[str], match_mode: str = "any", skip: int = 0, limit: int = 20) -> tuple[List[Product], int]:
        """根据标签搜索产品
        
        Args:
            db: 数据库会话
            tags: 搜索的标签列表
            match_mode: 匹配模式，'any'表示匹配任一标签，'all'表示匹配所有标签
            skip: 跳过的记录数
            limit: 返回的记录数限制
            
        Returns:
            tuple: (产品列表, 总数)
        """
        if not tags:
            return [], 0
            
        # 构建基础查询
        query = db.query(Product).filter(Product.product_tags.isnot(None))
        
        if match_mode == "all":
            # 匹配所有标签 - 每个标签都必须存在
            for tag in tags:
                query = query.filter(Product.product_tags.like(f'%"{tag}"%'))
        else:
            # 匹配任一标签 - 至少包含一个标签
            from sqlalchemy import or_
            tag_conditions = [Product.product_tags.like(f'%"{tag}"%') for tag in tags]
            query = query.filter(or_(*tag_conditions))
        
        # 按创建时间倒序排列
        query = query.order_by(Product.created_at.desc())
        
        # 获取总数
        total = query.count()
        
        # 分页查询
        products = query.offset(skip).limit(limit).all()
        
        return products, total

product_crud = ProductCRUD()